/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.sun.hunk.constant;

/**
 *
 * @author sunil
 */
public interface QueryConstant {
    String LIMIT = " LIMIT 10 ";
    String CLIENT_NAME_SELECT= "SELECT tp.account_name,tp.account_number,tp.account_type,ca.contact_name,ca.phone,ca.fax,ca.email1, ca.address1  FROM trading_partner tp LEFT JOIN customer_address ca ON ca.trading_partner_id=tp.id     WHERE ca.prime = 'on' AND tp.account_name LIKE '?%'  GROUP BY tp.account_number   " + LIMIT;
    String TRUCKER_SELECT="select tp.account_name,tp.account_number,ca.address1,ca.contact_name,ca.phone,ca.city,ca.state,ca.zip,ca.fax,ca.email1, tp.account_type " +
            " from trading_partner tp left join customer_address ca on ca.trading_partner_id=tp.id where tp.account_type like '%V%' and tp.account_name like'?%' " + LIMIT;
    String CLIENT_NUMBER_SELECT="select tp.account_number,tp.account_name,tp.account_type,ca.contact_name,ca.phone,ca.fax,ca.email1" +
            " from trading_partner tp left join customer_address ca on ca.trading_partner_id=tp.id where tp.account_number like'?%' " + LIMIT;
    String LOCATION_SELECT="select tp.account_name,ca.address1,ca.contact_name,ca.phone" +
            " from trading_partner tp left join customer_address ca on ca.trading_partner_id=tp.id where tp.account_name like'?%' "  + LIMIT;
    String ACCOUNT_NAME_SELECT="SELECT " +
            " tp.account_name" +
            ",tp.account_number" +
            ",ca.address1" +
            ",ca.city" +
            ",ca.state" +
            ",gc.description" +
            ",ca.contact_name" +
            ",ca.phone" +
            ",ca.fax" +
            ",ca.email1  " +
            ",ca.zip  " +
            "FROM trading_partner tp LEFT JOIN customer_address ca ON ca.trading_partner_id=tp.id " +
            "LEFT JOIN generic_code gc ON gc.id = ca.country" +
            " where tp.account_name like'?%' " + LIMIT;
    String CHARGE_CODE_SELECT = "SELECT CODE,description FROM generic_code WHERE code_type_id = " + CodeTypeIdConstant.CHARGE_CODE + " AND CODE LIKE '?%' " + LIMIT;
    String CHARGE_DESC_SELECT = "SELECT description,CODE FROM generic_code WHERE code_type_id = " + CodeTypeIdConstant.CHARGE_CODE + " AND description LIKE '?%' " + LIMIT;
    String COMMODITY_CODE_SELECT = "SELECT CODE,description FROM generic_code WHERE code_type_id = " + CodeTypeIdConstant.COMMODITY_CODE + " AND CODE LIKE '?%' " + LIMIT;
    String COMMODITY_DESC_SELECT = "SELECT description,CODE from generic_code WHERE  code_type_id = " + CodeTypeIdConstant.COMMODITY_DESC + " AND description like '?%' " + LIMIT;
    String TYPE_OF_MOVE_SELECT = "select description from generic_code where code_type_id = " + CodeTypeIdConstant.TYPE_OF_MOVE;
    String FREQUENCY_OF_SERVICE_SELECT = "select description from generic_code where code_type_id = " + CodeTypeIdConstant.FREQUENCY_OF_SERVICE + " and description like '?%' " + LIMIT;
    String TRANSPORTATION_MODE_SELECT = "select description from generic_code where code_type_id = " + CodeTypeIdConstant.TRANSPORTATION_MODE + " and description like '?%' " + LIMIT;
    String VESSEL_SELECT = "select description from generic_code where code_type_id = " + CodeTypeIdConstant.VESSEL + " and description like '?%' " + LIMIT;
    String PORTS_SELECT="SELECT CONCAT(UPPER(p.port_name),',','  ',state.code), p.id , UPPER(p.port_name), state.code FROM ports p JOIN un_location u ON p.un_location_code = u.un_loc_code JOIN generic_code state ON state.id = u.state_code WHERE p.port_name like '?%' ORDER BY p.port_name"+ LIMIT;
    String BL_FILENUMBER_SELECT="SELECT file_number FROM file_number WHERE lcl_bl_id IS NOT NULL and file_number like '?%' " + LIMIT;;
}
